#!/bin/sh

#
# This file is released under the terms of the Artistic License.
# Please see the file LICENSE, included in this package, for details.
#
# Copyright (C) 2007-2010 Mark Wong
#

#
# If the SQL in funcs.c is updated, this file should be updated to match.
#

explain() {
	SQL="EXPLAIN ${@}"
	psql -e ${PORTARG} ${DBNAMEARG} -c "${SQL}" >> ${OUTPUT_FILE} 2>&1
}

while getopts "d:o:p:" opt; do
	case ${opt} in
	d)
		DBT5DBNAME=${OPTARG}
		;;
	o)
		OUTPUT_FILE=${OPTARG}
		;;
	p)
		PORT=${OPTARG}
		;;
	esac
done

if [ ! "x${DBT5DBNAME}" = "x" ]; then
	DBNAMEARG="-d ${DBT5DBNAME}"
fi

if [ ! "x${PORT}" = "x" ]; then
	PORTARG="-p ${PORT}"
fi

if [ "x${OUTPUT_FILE}" = "x" ]; then
	echo "usage: db-plans -o <outfile>"
	exit 1
fi

echo "-------------------------" > ${OUTPUT_FILE}
echo "Broker Volume Transaction" >> ${OUTPUT_FILE}
echo "-------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT b_name, SUM(tr_qty * tr_bid_price)
FROM trade_request, sector, industry, company, broker,
     security
WHERE tr_b_id = b_id
  AND tr_s_symb = s_symb
  AND s_co_id = co_id
  AND co_in_id = in_id
  AND sc_id = in_sc_id
  AND b_name = ANY ('{a,b}')
  AND sc_name = '%s'
GROUP BY b_name
ORDER BY 2 DESC
"

echo "-----------------------------" >> ${OUTPUT_FILE}
echo "Customer Position Transaction" >> ${OUTPUT_FILE}
echo "-----------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT  c_id
FROM    customer
WHERE   c_tax_id = 'a';
"

explain "
SELECT c_st_id,
       c_l_name,
       c_f_name,
       c_m_name,
       c_gndr,
       c_tier,
       c_dob,
       c_ad_id,
       c_ctry_1,
       c_area_1,
       c_local_1,
       c_ext_1,
       c_ctry_2,
       c_area_2,
       c_local_2,
       c_ext_2,
       c_ctry_3,
       c_area_3,
       c_local_3,
       c_ext_3,
       c_email_1,
       c_email_2
FROM   customer
WHERE  c_id = 1
"
explain "
SELECT ca_id, ca_bal, COALESCE(SUM(hs_qty * lt_price)) AS soma
FROM   customer_account
       LEFT OUTER JOIN holding_summary
                    ON hs_ca_id = ca_id,
       last_trade
WHERE  ca_c_id = 1
  AND lt_s_symb = hs_s_symb
GROUP BY ca_id, ca_bal
ORDER BY 3 ASC
LIMIT 10
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 2" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT   t_id,
         t_s_symb,
         t_qty,
         st_name,
         th_dts
FROM     (SELECT   t_id AS id
          FROM     trade
          WHERE    t_ca_id = 1
          ORDER BY t_dts DESC
          LIMIT 10) AS t,
         trade,
         trade_history,
         status_type
WHERE    t_id = id
         AND th_t_id = t_id
         AND st_id = th_st_id
ORDER BY th_dts DESC
LIMIT 30
"

echo "----------------------------" >> ${OUTPUT_FILE}
echo "Data Maintenance Transaction" >> ${OUTPUT_FILE}
echo "----------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT ap_acl
FROM account_permission
WHERE ap_ca_id = 1
ORDER BY ap_acl DESC
LIMIT 1
"

explain "
UPDATE account_permission
SET ap_acl = '%s'
WHERE ap_ca_id = 1
  AND ap_acl = '%s'
"

explain "
SELECT ad_line2, ad_id
FROM address, customer
WHERE ad_id = c_ad_id
  AND c_id = 1
"

explain "
SELECT ad_line2, ad_id
FROM address, company
WHERE ad_id = co_ad_id
  AND co_id = 1
"

explain "
UPDATE address
SET ad_line2 = '%s'
WHERE ad_id = 1

"

explain "
SELECT co_sp_rate
FROM company
WHERE co_id = 1
"

explain "
UPDATE company
SET co_sp_rate = '%s'
WHERE co_id = 1
"

explain "
SELECT c_email_2
FROM customer
WHERE c_id = 1
"

explain "
UPDATE customer
SET c_email_2 = SUBSTRING(c_email_2
                          FROM '#\"%%@#\"%%'
                          FOR '#') || '%s'
WHERE c_id = 1
"

explain "
SELECT cx_tx_id
FROM customer_taxrate
WHERE cx_c_id = 1
  AND (cx_tx_id LIKE 'US%%' OR cx_tx_id LIKE 'CN%%')
"

explain "
UPDATE customer_taxrate
SET cx_tx_id = '%s'
WHERE cx_c_id = 1
  AND cx_tx_id = '%s'
"

explain "
UPDATE daily_market
SET dm_vol = dm_vol + 1
WHERE dm_s_symb = '%s'
  AND EXTRACT(DAY FROM dm_date) = 1
"

explain "
SELECT COUNT(*)
FROM exchange
WHERE ex_desc LIKE '%LAST UPDATED%'
"

explain "
SELECT COUNT(*)
FROM exchange
WHERE ex_desc LIKE '%LAST UPDATED%'
"

explain "
UPDATE exchange
SET ex_desc = ex_desc || ' LAST UPDATED ' || now()
"

explain "
UPDATE exchange
SET ex_desc = SUBSTRING(ex_desc || ' LAST UPDATED ' || NOW()
                        FROM 1 FOR (CHAR_LENGTH(ex_desc) -
                                    CHAR_LENGTH(NOW()::TEXT))) || NOW()
"

explain "
SELECT COUNT(*)
FROM financial
WHERE fi_co_id = 1
  AND EXTRACT(DAY FROM fi_qtr_start_date) = 1
"

explain "
UPDATE financial
SET fi_qtr_start_date = fi_qtr_start_date + INTERVAL '1 day'
WHERE fi_co_id = 1
"

explain "
UPDATE financial
SET fi_qtr_start_date = fi_qtr_start_date - INTERVAL '1 day'
WHERE fi_co_id = 1"

explain "
UPDATE news_item
SET ni_dts = ni_dts + INTERVAL '1 day'
WHERE ni_id IN (SELECT nx_ni_id
               FROM news_xref
               WHERE nx_co_id = 1)
"

explain "
UPDATE security
SET s_exch_date = s_exch_date + INTERVAL '1 day'
WHERE s_symb = '%s'
"

explain "
SELECT tx_name
FROM taxrate
WHERE tx_id = '%s'
"

explain "
UPDATE taxrate
SET tx_name = '%s'
WHERE tx_id = '%s'"

explain "
SELECT count(*)
FROM watch_item, watch_list
WHERE wl_c_id = 1
  AND wi_wl_id = wl_id
"

explain "
SELECT wi_s_symb
FROM (SELECT wi_s_symb
      FROM watch_item, watch_list
      WHERE wl_c_id = 1
        AND wi_wl_id = wl_id
      ORDER BY wi_s_symb ASC) AS foo
OFFSET 1
LIMIT 1
"

explain "
SELECT s_symb
FROM security
WHERE s_symb > '%s'
  AND s_symb NOT IN (SELECT wi_s_symb
                     FROM watch_item, watch_list
                     WHERE wl_c_id = 1
                       AND wi_wl_id = wl_id)
ORDER BY s_symb ASC
LIMIT 1
"

explain "
UPDATE watch_item
SET wi_s_symb = '%s'
FROM watch_list
WHERE wl_c_id = 1
  AND wi_wl_id = wl_id
  AND wi_s_symb = '%s'
"

echo "-----------------------" >> ${OUTPUT_FILE}
echo "Market Feed Transaction" >> ${OUTPUT_FILE}
echo "-----------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
UPDATE last_trade
SET    lt_price = 1,
       lt_vol = lt_vol + 1,
       lt_dts = now()
WHERE  lt_s_symb = '%s'
RETURNING lt_s_symb
"

explain "
SELECT tr_t_id,
       tr_bid_price,
       tr_tt_id,
       tr_qty
FROM   trade_request
WHERE  tr_s_symb = '%s'
       AND ((tr_tt_id = '%s'
             AND tr_bid_price >= 1)
             OR (tr_tt_id = '%s'
                 AND tr_bid_price <= 1)
             OR (tr_tt_id = '%s'
                 AND tr_bid_price >= 1))
"

explain "
UPDATE trade
SET    t_dts = now(),
       t_st_id = '%s'
WHERE  t_id = 1
"

explain "
DELETE FROM trade_request
WHERE  tr_t_id = 1
"

explain "
INSERT INTO trade_history(th_t_id, th_dts, th_st_id)
VALUES (1, now(), '%s')
"

echo "------------------------" >> ${OUTPUT_FILE}
echo "Market Watch Transaction" >> ${OUTPUT_FILE}
echo "------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}


explain "
SELECT wi_s_symb
FROM   watch_item,
       watch_list
WHERE  wi_wl_id = wl_id
       AND wl_c_id = 1
"

explain "
SELECT s_symb
FROM   industry,
       company,
       security
WHERE  in_name = '%s'
       AND co_in_id = in_id
       AND co_id BETWEEN 1
                         AND 2
       AND s_co_id = co_id
"

explain "
SELECT hs_s_symb
FROM   holding_summary
WHERE  hs_ca_id = 1
"

explain "
SELECT lt_price
FROM   last_trade
WHERE  lt_s_symb = '%s'
"

explain "
SELECT s_num_out
FROM   security
WHERE  s_symb = '%s'
"

explain "
SELECT dm_close
FROM   daily_market
WHERE  dm_s_symb = '%s'
       AND dm_date = '2002-02-22'
"

echo "---------------------------" >> ${OUTPUT_FILE}
echo "Security Detail Transaction" >> ${OUTPUT_FILE}
echo "---------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT s_name,
       co_id,
       co_name,
       co_sp_rate,
       co_ceo,
       co_desc,
       co_open_date,
       co_st_id,
       ca.ad_line1,
       ca.ad_line2,
       zca.zc_town,
       zca.zc_div,
       ca.ad_zc_code,
       ca.ad_ctry,
       s_num_out,
       s_start_date,
       s_exch_date,
       s_pe,
       s_52wk_high,
       s_52wk_high_date,
       s_52wk_low,
       s_52wk_low_date,
       s_dividend,
       s_yield,
       zea.zc_div,
       ea.ad_ctry,
       ea.ad_line1,
       ea.ad_line2,
       zea.zc_town,
       ea.ad_zc_code,
       ex_close,
       ex_desc,
       ex_name,
       ex_num_symb,
       ex_open
FROM   security,
       company,
       address ca,
       address ea,
       zip_code zca,
       zip_code zea,
       exchange
WHERE  s_symb = '%s'
       AND co_id = s_co_id
       AND ca.ad_id = co_ad_id
       AND ea.ad_id = ex_ad_id
       AND ex_id = s_ex_id
       AND ca.ad_zc_code = zca.zc_code
       AND ea.ad_zc_code = zea.zc_code
AND ea.ad_zc_code = zea.zc_code
"

explain "
SELECT co_name,
       in_name
FROM   company_competitor,
       company,
       industry
WHERE  cp_co_id = 1
       AND co_id = cp_comp_co_id
       AND in_id = cp_in_id
LIMIT 1
"

explain "
SELECT   fi_year,
         fi_qtr,
         fi_qtr_start_date,
         fi_revenue,
         fi_net_earn,
         fi_basic_eps,
         fi_dilut_eps,
         fi_margin,
         fi_inventory,
         fi_assets,
         fi_liability,
         fi_out_basic,
         fi_out_dilut
FROM     financial
WHERE    fi_co_id = 1
ORDER BY fi_year ASC,
         fi_qtr
LIMIT 1
"

explain "
SELECT   dm_date,
         dm_close,
         dm_high,
         dm_low,
         dm_vol
FROM     daily_market
WHERE    dm_s_symb = '%s'
         AND dm_date >= '2000-01-01'
ORDER BY dm_date ASC
LIMIT 1
"

explain "
SELECT lt_price,
       lt_open_price,
       lt_vol
FROM   last_trade
WHERE  lt_s_symb = '%s'
"

explain "
SELECT ni_item,
       ni_dts,
       ni_source,
       ni_author,
      '',
      ''
FROM   news_xref,
       news_item
WHERE  ni_id = nx_ni_id
       AND nx_co_id = 1
LIMIT 1
"

explain "
SELECT '',
       ni_dts,
       ni_source,
       ni_author,
       ni_headline,
       ni_summary
FROM   news_xref,
       news_item
WHERE  ni_id = nx_ni_id
       AND nx_co_id = 1
LIMIT 1
"


echo "-------------------------" >> ${OUTPUT_FILE}
echo "Trade Cleanup Transaction" >> ${OUTPUT_FILE}
echo "-------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT tr_t_id
FROM trade_request
ORDER BY tr_t_id
"

explain "
INSERT INTO trade_history(th_t_id, th_dts, th_st_id)
VALUES (1, now(), '%s')
"

explain "
DELETE FROM trade_request
"

explain "
SELECT t_id
FROM trade
WHERE t_id >= 1
  AND t_st_id = '%s'
"

explain "
UPDATE trade
SET t_st_id = '%s',
    t_dts = now()
WHERE t_id = 1
RETURNING t_dts
"

explain "
INSERT INTO trade_history(th_t_id, th_dts, th_st_id)
VALUES (1, now(), '%s')
"

echo "------------------------" >> ${OUTPUT_FILE}
echo "Trade Lookup Transaction" >> ${OUTPUT_FILE}
echo "------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_bid_price, t_exec_name, t_is_cash, tt_is_mrkt,
       t_trade_price
FROM trade, trade_type
WHERE t_id = 1
  AND t_tt_id = tt_id
"

explain "
SELECT se_amt, se_cash_due_date, se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
SELECT ct_amt, ct_dts, ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

explain "
SELECT th_dts, th_st_id
FROM trade_history
WHERE th_t_id = 1
ORDER BY th_dts
LIMIT 3
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 2" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_bid_price, t_exec_name, t_is_cash, t_id, t_trade_price
FROM trade
WHERE t_ca_id = 1
  AND t_dts >= '2010-01-01 00:00:00'
  AND t_dts <= '2010-01-01 00:00:00'
ORDER BY t_dts
LIMIT 1
"

explain "
SELECT se_amt, se_cash_due_date, se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
SELECT ct_amt, ct_dts, ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 3" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_ca_id, t_exec_name, t_is_cash, t_trade_price, t_qty,
       t_dts, t_id, t_tt_id
FROM trade
WHERE t_s_symb = '%s'
  AND t_dts >= '2010-01-01 00:00:00'
  AND t_dts <= '2010-01-01 00:00:00'
ORDER BY t_dts ASC
LIMIT 1
"

explain "
SELECT se_amt, se_cash_due_date, se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
SELECT ct_amt, ct_dts, ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

explain "
SELECT th_dts, th_st_id
FROM trade_history
WHERE th_t_id = 1
ORDER BY th_dts ASC
LIMIT 3
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 4" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_id
FROM trade
WHERE t_ca_id = 1
  AND t_dts >= '2010-01-01 00:00:00'
ORDER BY t_dts ASC
LIMIT 1
"

explain "
SELECT hh_h_t_id, hh_t_id, hh_before_qty, hh_after_qty
FROM holding_history
WHERE hh_h_t_id IN (
      SELECT hh_h_t_id
      FROM holding_history
      WHERE hh_t_id = 1)
"

echo "-----------------------" >> ${OUTPUT_FILE}
echo "Trade Order Transaction" >> ${OUTPUT_FILE}
echo "-----------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT ca_name, ca_b_id, ca_c_id, ca_tax_st
FROM customer_account
WHERE ca_id = 1
"

explain "
SELECT c_f_name, c_l_name, c_tier, c_tax_id
FROM customer
WHERE c_id = 1
"

explain "
SELECT b_name
FROM Broker
WHERE b_id = 1
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 2" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT ap_acl
FROM account_permission
WHERE ap_ca_id = 1
  AND ap_f_name = '%s'
  AND ap_l_name = '%s'
  AND ap_tax_id = '%s'
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 3" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT co_id
FROM company
WHERE co_name = e'%s'
"

explain "
SELECT s_ex_id, s_name, s_symb
FROM security
WHERE s_co_id = 1
  AND s_issue = '%s'
"

explain "
SELECT s_co_id, s_ex_id, s_name
FROM security
WHERE s_symb = '%s'
"

explain "
SELECT co_name
FROM company
WHERE co_id = 1
"

explain "
SELECT lt_price
FROM last_trade
WHERE lt_s_symb = '%s'
"

explain "
SELECT tt_is_mrkt, tt_is_sell
FROM trade_type
WHERE tt_id = '%s'
"

explain "
SELECT hs_qty
FROM holding_summary
WHERE hs_ca_id = 1
  AND hs_s_symb = '%s'
"

explain "
SELECT h_qty, h_price
FROM holding
WHERE h_ca_id = 1
  AND h_s_symb = '%s'
ORDER BY h_dts DESC
"

explain "
SELECT h_qty, h_price
FROM holding
WHERE h_ca_id = 1
  AND h_s_symb = '%s'
ORDER BY h_dts ASC
"

explain "
SELECT sum(tx_rate)
FROM taxrate
WHERE tx_id in (
                SELECT cx_tx_id
                FROM customer_taxrate
                WHERE cx_c_id = 1)
"

explain "
SELECT cr_rate
FROM commission_rate
WHERE cr_c_tier = 1
  AND cr_tt_id = '%s'
  AND cr_ex_id = '%s'
  AND cr_from_qty <= 1
  AND cr_to_qty >= 1
"

explain "
SELECT ch_chrg
FROM charge
WHERE ch_c_tier = 1
  AND ch_tt_id = '%s'
"

explain "
SELECT ca_bal
FROM customer_account
WHERE ca_id = 1
"

explain "
SELECT sum(hs_qty * lt_price)
FROM holding_summary, last_trade
WHERE hs_ca_id = 1
  AND lt_s_symb = hs_s_symb
"

explain "
INSERT INTO trade(t_id, t_dts, t_st_id, t_tt_id, t_is_cash,
                  t_s_symb, t_qty, t_bid_price, t_ca_id,
                  t_exec_name, t_trade_price, t_chrg, t_comm,
                  t_tax, t_lifo)
VALUES (NEXTVAL('seq_trade_id'), now(), '%s', '%s', true, '%s',
        1, 1, 1, '%s', NULL, 1, 1, 0, true)
RETURNING t_id, t_dts
"

explain "
INSERT INTO trade_request(tr_t_id, tr_tt_id, tr_s_symb, tr_qty,
                          tr_bid_price, tr_b_id)
VALUES (1, '%s', '%s', 1, 1, 1)

"

explain "
INSERT INTO trade_history(th_t_id, th_dts, th_st_id)
VALUES(1, now(), '%s')
"

echo "------------------------" >> ${OUTPUT_FILE}
echo "Trade Result Transaction" >> ${OUTPUT_FILE}
echo "------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_ca_id, t_tt_id, t_s_symb, t_qty, t_chrg,
       CASE WHEN t_lifo = true
            THEN 1
            ELSE 0 END,
       CASE WHEN t_is_cash = true
            THEN 1
            ELSE 0 END
FROM trade
WHERE t_id = 1
"

explain "
SELECT tt_name,
       CASE WHEN tt_is_sell = true
            THEN 1
            ELSE 0 END,
       CASE WHEN tt_is_mrkt = true
            THEN 1
            ELSE 0 END
FROM trade_type
WHERE tt_id = '%s'
"

explain "
SELECT hs_qty
FROM holding_summary
WHERE hs_ca_id = 1
  AND hs_s_symb = '%s'
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 2" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT ca_b_id, ca_c_id, ca_tax_st
FROM customer_account
WHERE ca_id = 1
"

explain "
INSERT INTO holding_summary(hs_ca_id, hs_s_symb, hs_qty)
VALUES(1, '%s', 1)
"

explain "
UPDATE holding_summary
SET hs_qty = 1
WHERE hs_ca_id = 1
  AND hs_s_symb = '%s'
"

explain "
SELECT h_t_id, h_qty, h_price
FROM holding
WHERE h_ca_id = 1
  AND h_s_symb = '%s'
ORDER BY h_dts DESC
"

explain "
SELECT h_t_id, h_qty, h_price
FROM holding
WHERE h_ca_id = 1
  AND h_s_symb = '%s'
ORDER BY h_dts ASC
"

explain "
INSERT INTO holding_history(hh_h_t_id, hh_t_id, hh_before_qty,
                            hh_after_qty)
VALUES(1, 1, 1, 1)
"

explain "
UPDATE holding
SET h_qty = 1
WHERE h_t_id = 1
"

explain "
DELETE FROM holding
WHERE h_t_id = 1
"

explain "
INSERT INTO holding(h_t_id, h_ca_id, h_s_symb, h_dts, h_price,
                    h_qty)
VALUES (1, 1, '%s', '2010-01-01 00:00:00', 1, 1)
"

explain "
DELETE FROM holding_summary
WHERE hs_ca_id = 1
  AND hs_s_symb = '%s'
"

explain "
INSERT INTO holding_summary(hs_ca_id, hs_s_symb, hs_qty)
VALUES (1, '%s', 1)
"

explain "
UPDATE holding_summary
SET hs_qty = 1
WHERE hs_ca_id = 1
  AND hs_s_symb = '%s'
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 3" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT SUM(tx_rate)
FROM taxrate
WHERE tx_id IN (SELECT cx_tx_id
                FROM customer_taxrate
                WHERE cx_c_id = 1)
"

explain "
UPDATE trade
SET t_tax = 1
WHERE t_id = 1
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 4" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}


explain "
SELECT s_ex_id, s_name
FROM security
WHERE s_symb = '%s'
"

explain "
SELECT c_tier
FROM customer
WHERE c_id = 1
"

explain "
SELECT cr_rate
FROM commission_rate
WHERE cr_c_tier = 1
  AND cr_tt_id = '%s'
  AND cr_ex_id = '%s'
  AND cr_from_qty <= 1
  AND cr_to_qty >= 1
LIMIT 1
"


echo "-------" >> ${OUTPUT_FILE}
echo "Frame 5" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
UPDATE trade
SET t_comm = 1,
    t_dts = '2010-01-01 00:00:00',
    t_st_id = '%s',
    t_trade_price = 1
WHERE t_id = 1
"

explain "
INSERT INTO trade_history(th_t_id, th_dts, th_st_id)
VALUES (1, '2010-01-01 00:00:00', '%s')
"

explain "
UPDATE broker
SET b_comm_total = b_comm_total + 1,
    b_num_trades = b_num_trades + 1
WHERE b_id = 1
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 6" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
INSERT INTO settlement(se_t_id, se_cash_type, se_cash_due_date,
                       se_amt)
VALUES (1, '%s', '2000-01-01', 1)
"

explain "
UPDATE customer_account
SET ca_bal = ca_bal + 1
WHERE ca_id = 1
"

explain "
INSERT INTO cash_transaction(ct_dts, ct_t_id, ct_amt, ct_name)
VALUES ('2010-01-01 00:00:00', 1, 1, e'%s 1 shared of %s')
"

explain "
SELECT ca_bal
FROM customer_account
WHERE ca_id = 1
"

echo "------------------------" >> ${OUTPUT_FILE}
echo "Trade Status Transaction" >> ${OUTPUT_FILE}
echo "------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT  C_L_NAME, C_F_NAME, B_NAME
FROM    CUSTOMER_ACCOUNT, CUSTOMER, BROKER
WHERE   CA_ID = 1
  AND   C_ID = CA_C_ID
  AND   B_ID = CA_B_ID;

"

explain "
SELECT c_l_name, c_f_name, b_name
FROM customer_account, customer, broker
WHERE ca_id = 1
  AND c_id = ca_c_id
  AND b_id = ca_b_id
"

echo "------------------------" >> ${OUTPUT_FILE}
echo "Trade Update Transaction" >> ${OUTPUT_FILE}
echo "------------------------" >> ${OUTPUT_FILE}

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 1" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}


explain "
SELECT t_exec_name
FROM trade
WHERE t_id = 1
"

explain "
UPDATE trade
SET t_exec_name = '%s'
WHERE t_id = 1
"

explain "
SELECT t_bid_price, t_exec_name, t_is_cash, tt_is_mrkt,
       t_trade_price
FROM trade, trade_type
WHERE t_id = 1
  AND t_tt_id = tt_id
"

explain "
SELECT se_amt, se_cash_due_date, se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
SELECT ct_amt, ct_dts, ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

explain "
SELECT th_dts, th_st_id
FROM trade_history
WHERE th_t_id = 1
ORDER BY th_dts
LIMIT 3
"


echo "-------" >> ${OUTPUT_FILE}
echo "Frame 2" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_bid_price, t_exec_name, t_is_cash, t_id, t_trade_price
FROM trade
WHERE t_ca_id = 1
  AND t_dts >= '2010-01-01 00:00:00'
  AND t_dts <= '2010-01-01 00:00:00'
ORDER BY t_dts ASC
LIMIT 1
"

explain "
SELECT se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
UPDATE settlement
SET se_cash_type = '%s'
WHERE se_t_id = 1
"

explain "
SELECT se_amt, se_cash_due_date, se_cash_type
FROM settlement
WHERE se_t_id = 1
"

explain "
SELECT ct_amt, ct_dts, ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

explain "
SELECT th_dts, th_st_id
FROM trade_history
WHERE th_t_id = 1
ORDER BY th_dts
LIMIT 3
"

echo "-------" >> ${OUTPUT_FILE}
echo "Frame 3" >> ${OUTPUT_FILE}
echo "-------" >> ${OUTPUT_FILE}

explain "
SELECT t_ca_id, t_exec_name, t_is_cash, t_trade_price, t_qty,
       s_name, t_dts, t_id, t_tt_id, tt_name
FROM trade, trade_type, security
WHERE t_s_symb = '%s'
  AND t_dts >= '2010-01-01 00:00:00'
  AND t_dts <= '2010-01-01 00:00:00'
  AND tt_id = t_tt_id
  AND s_symb = t_s_symb
ORDER BY t_dts ASC
LIMIT 1
"

explain "
SELECT ct_name
FROM cash_transaction
WHERE ct_t_id = 1
"

explain "
UPDATE cash_transaction
SET ct_name = e'%s'
WHERE ct_t_id = 1
"
